﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace FrbaCommerce
{
    public class ConexionSql
    {
        //public Conexion_sql()
        //{

        //}

        public SqlConnection conexion()
        {
            try
            {
                SqlConnection conex = new SqlConnection(Properties.Settings.Default.ConnectionString);
                return conex;
            }
            catch (Exception ex)
            { throw ex; }
        }

        public DataSet Consulta(DataSet Dts, String SP)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter dad;
                DataSet data = new DataSet();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion();
                cmd.CommandText = SP;
                for (int i = 0; i < Dts.Tables["Consulta"].Columns.Count; i++)
                {
                    cmd.Parameters.AddWithValue('@' + Dts.Tables["Consulta"].Columns[i].Caption, Dts.Tables["Consulta"].Rows[0][i]);
                }
                dad = new SqlDataAdapter(cmd);
                dad.Fill(data, "Consulta");
                return data;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet ejecutarConsulta(string query)
        {
            if (query.Length == 0)
            {
                throw (new Exception());
            }

            try
            {
                DataSet dato = new DataSet();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(query, conexion());
                dataAdapter.Fill(dato);
                return dato;
            }
            catch (SqlException exsql)
            {
                throw exsql;
            }
        }

    }
}
